Imports System.Data.OracleClient
Namespace persistent
    Public Class GhhServicesTariff

        Public Shared Function GetTariff(ByVal co_id As Integer, ByVal sncode As Integer) As DataTable
            Dim strSQL As String

            strSQL = " select   c.tmcode tmcode ,max(c.des) des from SYSADM.MPULKTMB a, sysadm.rateplan c "
            strSQL = strSQL + " where a.tmcode = c.tmcode and  c.des not like 'NOT%' and c.des not like 'NNOT%' "
            strSQL = strSQL + " and a.sncode = " + sncode.ToString()
            strSQL = strSQL + " and c.tmcode not in (select tmcode from contract_all where co_id =  " + co_id.ToString() + ")"
            strSQL = strSQL + " and c.tmcode in( select tmcode from SYSADM.MPULKNXG where prgcode in ( select prgcode from customer_all   where customer_id in(select customer_id from contract_all where co_id =  " + co_id.ToString() + " ) ) ) "
            strSQL = strSQL + "  group by c.tmcode order by max(c.des) "
            Return myDBConn.myORATable(strSQL)
        End Function
    End Class
End Namespace
